#/usr/bin/python3
#-×-coding:utf8 -*-
'''
    此代码将排课软件生成的db文件读取后，
    生成教室占用情况excel表。
    注：生成无格式。需自己分天及课时
'''
import sqlite3
#下面库都需要单独安装
import xlrd
import xlwt
import xlutils.copy

data = []
# row1 = ['1110', '1308', '1310', '1311', '1110', '1308', '1310', '1311', "1110", '1308', '1310', '1311', '1110', '1308', '1310', '1311', '1110', '1308', '1310', '1311']
# row2 = ['1312', '1402', '1404', '1410', '1312', '1402', '1404', '1410', '1312', '1402', '1404', '1410', '1312', '1402', '1404', '1410','1312', '1402', '1404', '1410']
# row3 = ['1510', '1205', '1207', '5机房', '1510', '1205', '1207', '5机房', '1510', '1205', '1207', '5机房', '1510', '1205', '1207', '5机房', '1510', '1205', '1207', '5机房']
# row4 = ['6机房', '7机房', '2机房', '3机房','6机房', '7机房', '2机房', '3机房','6机房', '7机房', '2机房', '3机房','6机房', '7机房', '2机房', '3机房', '6机房', '7机房', '2机房', '3机房']
# strNum = 0
# endNum =4
# workbook = xlwt.Workbook()
# for i in range(1, 21):
#     sheet = workbook.add_sheet("第" + str(i) +"周", cell_overwrite_ok=True)
#     for x in range(len(row1)):
#         sheet.write(0,x, row1[x])
#         sheet.write(2,x, row2[x])
#         sheet.write(4,x, row3[x])
#         sheet.write(6,x, row4[x])
#         sheet.write(8,x, row1[x])
#         sheet.write(10,x, row2[x])
#         sheet.write(12,x, row3[x])
#         sheet.write(14,x, row4[x])
#         sheet.write(16,x, row1[x])
#         sheet.write(18,x, row2[x])
#         sheet.write(20,x, row3[x])
#         sheet.write(22,x, row4[x])
#         sheet.write(24,x, row1[x])
#         sheet.write(26,x, row2[x])
#         sheet.write(28,x, row3[x])
#         sheet.write(30,x, row4[x])
# workbook.save("Test.xls")

#生成了一份标准表格，第一次使用时将上面注释打开即可，运行一次即可。


rb = xlrd.open_workbook("Test.xls")#表格路径，是上一个生成的表格路径
wb = xlutils.copy.copy(rb) # 复制了一份新表格
conn = sqlite3.connect(r'bs_pk(1).db')#数据库文件
c = conn.cursor()#链接
#语句
cursor = c.execute("SELECT Configuration_Info.Teaching_Time,Teacher_Info.Teacher_Name,Classroom_Info.Classroom_Name,Lesson_Info.Course_Name from Configuration_Info,Teacher_Info,Classroom_Info,Lesson_Info where Configuration_Info.Classroom_ID=Classroom_Info.Classroom_ID AND Configuration_Info.Teacher_ID=Teacher_Info.Teacher_ID AND Configuration_Info.Course_ID = Lesson_Info.Course_ID")
#将查询结果放入list
for i in cursor:
    data.append(i)

# for x in date:
#     print(x[1])
#     sheet = wb.sheet_by_index(int(x[0][0:3])-1)
#     row = sheet.nrows
#     col = sheet.ncols
#     for rown in range(row):
#         for coln in range(col):
#             cell = sheet.cell_value(rown,coln)
#             print(cell)
#sheet2 = xlutils.copy.copy(rb)
n = 0
#循环对比并添加
for i in data:
    week = int(i[0][0:2])-1
    day = int(i[0][2])
    course = int(i[0][3])
    sheet = rb.sheet_by_index(week)
    sheet2 = wb.get_sheet(week)
    for j in range((day-1)*4,day*4):
        for k in range((course-1)*8,course*8-1):
            if i[2] == sheet.cell_value(k,j):
                sheet2.write(k+1,j,'上课')
                n = n+1
print(n)
#写入新的表格，可随意命名，注意后缀
wb.save(r'234.xls')


conn.close()
